package db

import (
	//"database/sql"
	"fmt"
	"reflect"
	"server/conf"
	"strings"

	// "server/game"
	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
)

type user struct {
	Id       int    `db:"user_id"`
	Sex      int    `db:"sex"`
	UserName string `db:"username"`
	Email    string `db:"email"`
}

var SqlDb *sqlx.DB

func InitDB() {
	//"用户名:密码@[连接方式](主机名:端口号)/数据库名"
	connect := fmt.Sprintf("%s:%s@(127.0.0.1:3306)/%s", conf.Server.MySqlUsr, conf.Server.MySqlPwd, conf.Server.DbName)
	SqlDb = sqlx.MustConnect("mysql", connect) // 设置连接数据库的参数
}

// 查询一行数据
func QueryRow() {
	sqlStr := "SELECT id, name, age FROM user WHERE id = ?"

	var u user
	if err := SqlDb.Get(&u, sqlStr, 1); err != nil {
		fmt.Printf("get data failed, err:%v\n", err)
		return
	}
	fmt.Printf("id:%d, name:%s, age:%d\n", u.Id, u.UserName, u.Sex)
}

func QueryMultiRow() {
	sqlStr := "SELECT id, name, age FROM user WHERE id > ?"
	var users []user
	if err := SqlDb.Select(&users, sqlStr, 0); err != nil {
		fmt.Printf("get data failed, err:%v\n", err)
		return
	}

	for i := 0; i < len(users); i++ {
		fmt.Printf("id:%d, name:%s, age:%d\n", users[i].Id, users[i].UserName, users[i].Sex)
	}
}

// 插入数据
func InsertRow() {
	sqlStr := "INSERT INTO user(name, age) VALUES(?, ?)"
	result, err := SqlDb.Exec(sqlStr, "Meng小羽", 22)
	if err != nil {
		fmt.Printf("exec failed, err:%v\n", err)
		return
	}
	insertID, err := result.LastInsertId()
	if err != nil {
		fmt.Printf("get insert id failed, err:%v\n", err)
		return
	}
	fmt.Printf("insert data success, id:%d\n", insertID)
}

// 更新数据
func UpdateRow() {
	sqlStr := "UPDATE user SET age = ?, degree = ? WHERE id = ?"
	result, err := SqlDb.Exec(sqlStr, 22, 10, 4)
	if err != nil {
		fmt.Printf("exec failed, err:%v\n", err)
		return
	}
	affectedRows, err := result.RowsAffected()
	if err != nil {
		fmt.Printf("get affected failed, err:%v\n", err)
		return
	}
	fmt.Printf("update data success, affected rows:%d\n", affectedRows)
}

// 删除一行
func DeleteRow() {
	sqlStr := "DELETE FROM user WHERE id = ?"
	result, err := SqlDb.Exec(sqlStr, 4)
	if err != nil {
		fmt.Printf("exec failed, err:%v\n", err)
		return
	}
	affectedRows, err := result.RowsAffected()
	if err != nil {
		fmt.Printf("get affected failed, err:%v\n", err)
		return
	}
	fmt.Printf("delete data success, affected rows:%d\n", affectedRows)
}

// 绑定查询
func SelectNamedQuery() {
	sqlStr := "SELECT id, name, age FROM user WHERE age = :age1"
	rows, err := SqlDb.NamedQuery(sqlStr, map[string]interface{}{
		"age1": 22,
	})
	if err != nil {
		fmt.Printf("named query failed failed, err:%v\n", err)
		return
	}
	defer rows.Close()
	for rows.Next() {
		var u user
		if err := rows.StructScan(&u); err != nil {
			fmt.Printf("struct sacn failed, err:%v\n", err)
			continue
		}
		fmt.Printf("%#v\n", u)
	}
}

// 使用 named 方法插入数据
func InsertNamedExec() {
	sqlStr := "INSERT INTO user(name, age) VALUES(:name, :age)"
	result, err := SqlDb.NamedExec(sqlStr, map[string]interface{}{
		"name": "里斯",
		"age":  18,
	})
	if err != nil {
		fmt.Printf("named exec failed, err:%v\n", err)
		return
	}
	insertId, err := result.LastInsertId()
	if err != nil {
		fmt.Printf("get last insert id failed, err:%v\n", err)
		return
	}
	fmt.Printf("insert data success, id:%d\n", insertId)
}

// 事务操作
func UpdateTransaction() (err error) {
	tx, err := SqlDb.Begin()
	if err != nil {
		fmt.Printf("transaction begin failed, err:%v\n", err)
		return err
	}

	defer func() {
		if err != nil {
			fmt.Printf("transaction rollback")
			_ = tx.Rollback()
		} else {
			err = tx.Commit()
			fmt.Printf("transaction commit")
			return
		}
	}()

	sqlStr1 := "UPDATE user SET age = ? WHERE id = ? "
	reuslt1, err := tx.Exec(sqlStr1, 18, 1)
	if err != nil {
		fmt.Printf("sql exec failed, err:%v\n", err)
		return err
	}
	rows1, err := reuslt1.RowsAffected()
	if err != nil {
		fmt.Printf("affected rows is 0")
		return
	}
	sqlStr2 := "UPDATE user SET age = ? WHERE id = ? "
	reuslt2, err := tx.Exec(sqlStr2, 19, 5)
	if err != nil {
		fmt.Printf("sql exec failed, err:%v\n", err)
		return err
	}
	rows2, err := reuslt2.RowsAffected()
	if err != nil {
		fmt.Printf("affected rows is 0\n")
		return
	}

	if rows1 > 0 && rows2 > 0 {
		fmt.Printf("update data success\n")
	}
	return
}

func SeriesTransaction(sqlstrs []string, params [][]any) (err error) {
	tx, err := SqlDb.Begin()
	if err != nil {
		fmt.Printf("transaction begin failed, err:%v\n", err)
		return err
	}

	defer func() {
		if err != nil {
			fmt.Printf("transaction rollback")
			_ = tx.Rollback()
		} else {
			err = tx.Commit()
			fmt.Printf("transaction commit")
			return
		}
	}()

	for i := 0; i < len(sqlstrs); i++ {
		_, err := tx.Exec(sqlstrs[i], params[i]...)
		if err != nil {
			fmt.Printf("sql exec failed, err:%v\n", err)
			return err
		}

	}

	return
}

// 更新数据
func FormatUpdateOneAttrRow(u interface{}, tableName string, UpdateAttr string, Exclude string) (err error) {
	t := reflect.TypeOf(u)
	len1 := t.Elem().NumField()
	Fields := make([]string, len1)
	Values := make([]interface{}, len1)
	pp := reflect.ValueOf(u)
	origin := "UPDATE " + tableName + " SET "
	index := 0
	var keyValue interface{}
	for i := 0; i < len1; i++ {
		field := t.Elem().Field(i)
		if field.Tag.Get("db") != Exclude {
			if field.Tag.Get("db") == UpdateAttr {
				Fields[index] = field.Tag.Get("db") + " = ?"

				ufield := pp.Elem().FieldByName(field.Name)
				k := ufield.Kind()
				if k == reflect.String {
					Values[index] = ufield.String()
				}
				if k == reflect.Int32 {
					Values[index] = ufield.Int()
				}
				index++
			}

		} else {

			ufield := pp.Elem().FieldByName(field.Name)
			k := ufield.Kind()
			if k == reflect.String {
				keyValue = ufield.String()
			}
			if k == reflect.Int32 {
				keyValue = ufield.Int()
			}
		}
	}
	Values[index] = keyValue
	// Values = append(Values, keyValue)
	origin += strings.Join(Fields, ",")
	strLen := len(origin)
	origin = origin[:strLen-1]
	origin = origin + " WHERE " + Exclude + " = ?"
	sqlStr := origin
	fmt.Println("update sqlStr:", sqlStr)

	res, err := SqlDb.Exec(sqlStr, Values...)
	if err != nil {
		fmt.Printf("exec failed, err:%v\n", err)
		return
	}
	fmt.Printf("res: %v\n", res)
	// fmt.Printf("InsertId" + strconv.LastInsertId())

	return
}

// "UPDATE user SET age = ?, degree = ? WHERE id = ?"
func FormatAllMemUpdateDb(u interface{}, tableName string, Exclude string) (err error) {
	t := reflect.TypeOf(u)
	len1 := t.Elem().NumField()
	Fields := make([]string, len1)
	Values := make([]interface{}, len1)
	pp := reflect.ValueOf(u)
	origin := "UPDATE " + tableName + " SET "
	index := 0
	var keyValue interface{}
	for i := 0; i < len1; i++ {
		field := t.Elem().Field(i)
		if field.Tag.Get("db") != Exclude {
			Fields[index] = field.Tag.Get("db") + " = ?"

			ufield := pp.Elem().FieldByName(field.Name)
			k := ufield.Kind()
			if k == reflect.String {
				Values[index] = ufield.String()
			}
			if k == reflect.Int32 || k == reflect.Int {
				Values[index] = ufield.Int()
			}
			index++
		} else {

			ufield := pp.Elem().FieldByName(field.Name)
			k := ufield.Kind()
			if k == reflect.String {
				keyValue = ufield.String()
			}
			if k == reflect.Int32 || k == reflect.Int {
				keyValue = ufield.Int()
			}
		}
	}
	Values[index] = keyValue
	// Values = append(Values, keyValue)
	origin += strings.Join(Fields, ",")
	strLen := len(origin)
	origin = origin[:strLen-1]
	origin = origin + " WHERE " + Exclude + " = ?"
	sqlStr := origin
	fmt.Println("update sqlStr:", sqlStr)

	res, err := SqlDb.Exec(sqlStr, Values...)
	if err != nil {
		fmt.Printf("exec failed, err:%v\n", err)
		return
	}
	fmt.Printf("res: %v\n", res)
	// fmt.Printf("InsertId" + strconv.LastInsertId())

	return
}
func FormatAllMemInsertDb(u interface{}, tableName string) (insertID int64, err error) {
	t := reflect.TypeOf(u)
	len := t.Elem().NumField()
	Fields := make([]string, len)
	Fields1 := make([]string, len)
	FieldNames := make([]string, len)
	Values := make([]interface{}, len)
	pp := reflect.ValueOf(u)
	origin := "INSERT INTO " + tableName + "("
	for i := 0; i < len; i++ {
		field := t.Elem().Field(i)

		fmt.Println(field.Tag.Get("db"))
		Fields[i] = field.Tag.Get("db")
		Fields1[i] = "?"
		FieldNames[i] = field.Name

		ufield := pp.Elem().FieldByName(field.Name)
		k := ufield.Kind()
		if k == reflect.String {
			Values[i] = ufield.String()
		}
		if k == reflect.Int32 {
			Values[i] = ufield.Int()
		}
	}
	origin += strings.Join(Fields, ",")
	origin += ") VALUES("
	origin += strings.Join(Fields1, ",")
	origin += ")"
	sqlStr := origin
	ValuesT := make([]interface{}, 4)
	ValuesT[0] = 10001
	ValuesT[1] = "zxx001"
	ValuesT[2] = 2
	ValuesT[3] = 3
	fmt.Println("sqlStr:", sqlStr)
	//sqlStr = "INSERT INTO t_player_baseinfo(dwUin,user_name,energy,diamond) VALUES(?,?,?,?)"
	//result, err := SqlDb.Exec(sqlStr, ValuesT...)
	result, err := SqlDb.Exec(sqlStr, Values...)
	if err != nil {
		fmt.Printf("exec failed, err:%v\n", err)
		return
	}
	insertID, err = result.LastInsertId()
	if err != nil {
		fmt.Printf("get insert id failed, err:%v\n", err)
		return
	}
	fmt.Printf("insert data success, id:%d\n", insertID)

	return

}

func GetPlayerBaseInfoFromDbByName(name string) *ResPlayerBaseInfo {
	sqlStr := "SELECT * FROM t_player_baseinfo WHERE user_name = ?"
	var res ResPlayerBaseInfo
	if err := SqlDb.Get(&res, sqlStr, name); err != nil {
		fmt.Printf("get data failed, err:%v\n", err)
		return nil
	}
	return &res
}

func GetPlayerBaseInfoFromDbById(id int32) *ResPlayerBaseInfo {
	sqlStr := "SELECT * FROM t_player_baseinfo WHERE dwUin = ?"
	var res ResPlayerBaseInfo
	if err := SqlDb.Get(&res, sqlStr, id); err != nil {
		fmt.Printf("get data failed, err:%v\n", err)
		return nil
	}
	return &res
}

func GetAccountInfoFromDb(name string) *Db_Account {
	sqlStr := "SELECT * FROM t_account WHERE user_name = ?"
	var res Db_Account
	if err := SqlDb.Get(&res, sqlStr, name); err != nil {
		fmt.Printf("get data failed, err:%v\n", err)
		return nil
	}
	return &res
}
